﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Stu_sys.Entity;
using Stu_sys.Models;
using Stu_sys.utils;
using System.Text.Json;

namespace Stu_sys.Controllers
{
    public class StudentController : Controller
    {
        private readonly MysqlContext _mysqlContext;
        public StudentController(MysqlContext mysqlContext)
        {
            _mysqlContext = mysqlContext;
        }
        public IActionResult StuList()
        {
            return View();
        }
        [HttpGet]
        public IActionResult GetStudentList()
        {
            var attrStr = MyUtils.GetAttributeToJsonStr(typeof(StudentViewModel));
            var students =_mysqlContext.Students.ToList();

            var classList = _mysqlContext.ClassEntities.ToList();
            var gradeList=_mysqlContext.Grades.ToList();
            var depList=_mysqlContext.Departments.ToList();
            var bedList=_mysqlContext.Bedrooms.ToList();

            var res = from cr in students
                      join cl in classList on cr.ClassEntityId equals cl.ClassEntityId
                      join gl in gradeList on cl.GradeId equals gl.GradeId
                      join dl in depList on cl.DepartmentId equals dl.DepartmentId
                      join bl in bedList on cr.BedRoomId equals bl.BedRoomId
                      into cv
                      from sl in cv.DefaultIfEmpty()
                      select new
                      {
                          cr.StudentId,
                          cr.StudentName,
                          cr.StudentNum,
                          cr.Sex,
                          cl.ClassName,
                          gl.GradeName,
                          dl.DepName,
                          sl.BedRoomName,
                          cr.IDNumber,
                          cr.Birthday,
                          cr.Nation,
                          cr.Political,
                          cr.FatherName,
                          cr.FatherPhone,
                          cr.MotherName,
                          cr.MotherPhone,
                          cr.Address
                      };

            var studentStr = JsonSerializer.Serialize(res);
            var data = new
            {
                attr = attrStr,
                row = studentStr
            };
            return Json(data);
        }


        [HttpPost]
        public IActionResult AddStudent(Student student) 
        {
            _mysqlContext.Students.Add(student);
            var res = _mysqlContext.SaveChanges();
            return Json(res);
        }


        [HttpPost]
        public IActionResult GetStudentById(Student student)
        { 
            var stu=_mysqlContext.Students.FirstOrDefault(s=>s.StudentId==student.StudentId);

            var classEntity = _mysqlContext.ClassEntities.FirstOrDefault(c => c.ClassEntityId == stu.ClassEntityId);
            var grade = _mysqlContext.Grades.FirstOrDefault(g => g.GradeId == classEntity.GradeId);
            var dep = _mysqlContext.Departments.FirstOrDefault(d => d.DepartmentId == classEntity.DepartmentId);

            var res = new { 
                stu?.StudentId,
                stu?.StudentName,
                stu?.StudentNum,
                stu?.Sex,
                stu?.Nation,
                stu?.ClassEntityId,
                grade?.GradeId,
                dep?.DepartmentId,
                stu?.IDNumber,
                stu?.Birthday,
                stu?.BedRoomId,
                stu?.Address,
                stu?.FatherName,
                stu?.FatherPhone,
                stu?.MotherName,
                stu?.MotherPhone
            };
            
            return Json(res);
        }

        [HttpPost]
        public IActionResult EditStudent(Student student)
        {
            if (student.StudentName == null)
            {
                return Json(-1);
            }

            var stu = _mysqlContext.Students.Where(s=>s.StudentId==student.StudentId).FirstOrDefault();
            stu.StudentName=student.StudentName;
            stu.StudentNum=student.StudentNum;
            stu.Sex=student.Sex;
            stu.Nation=student.Nation;
            stu.ClassEntityId=student.ClassEntityId;
            stu.IDNumber=student.IDNumber;
            stu.Birthday=student.Birthday;
            stu.Political=student.Political;
            stu.BedRoomId=student.BedRoomId;
            stu.Address=student.Address;
            stu.FatherName=student.FatherName;
            stu.FatherPhone =student.FatherPhone;
            stu.MotherName  =student.MotherName;
            stu.MotherPhone =student.MotherPhone;

            _mysqlContext.Students.Update(stu);
            var res = _mysqlContext.SaveChanges();
            return Json(res);
        }


        [HttpPost]
        public IActionResult DelStudent(Student student)
        {
            if (student == null)
            {
                return Json(-1);
            }
            var d = _mysqlContext.Students.Single(s=>s.StudentId==student.StudentId);
            _mysqlContext.Remove(d);
            var res = _mysqlContext.SaveChanges();
            return Json(res);
        }


        [HttpPost]
        public IActionResult SearchStudent(Student student)
        {
            var attrStr = MyUtils.GetAttributeToJsonStr(typeof(StudentViewModel));
            var students = _mysqlContext.Students.Where(c => c.StudentName.Contains(student.StudentName))
                .ToList();

            var classList = _mysqlContext.ClassEntities.ToList();
            var gradeList = _mysqlContext.Grades.ToList();
            var depList = _mysqlContext.Departments.ToList();
            var bedList = _mysqlContext.Bedrooms.ToList();

            var res = from cr in students
                      join cl in classList on cr.ClassEntityId equals cl.ClassEntityId
                      join gl in gradeList on cl.GradeId equals gl.GradeId
                      join dl in depList on cl.DepartmentId equals dl.DepartmentId
                      join bl in bedList on cr.BedRoomId equals bl.BedRoomId
                      into cv
                      from sl in cv.DefaultIfEmpty()
                      select new
                      {
                          cr.StudentId,
                          cr.StudentName,
                          cr.StudentNum,
                          cr.Sex,
                          cl.ClassName,
                          gl.GradeName,
                          dl.DepName,
                          sl.BedRoomName,
                          cr.IDNumber,
                          cr.Birthday,
                          cr.Nation,
                          cr.Political,
                          cr.FatherName,
                          cr.FatherPhone,
                          cr.MotherName,
                          cr.MotherPhone,
                          cr.Address
                      };

            var studentStr = JsonSerializer.Serialize(res);
            var data = new
            {
                attr = attrStr,
                row = studentStr
            };
            return Json(data);
        }


        [HttpPost]
        public IActionResult GetStudentByGradeId(Grade grade)
        { 
            var gr = _mysqlContext.Grades.FirstOrDefault(g => g.GradeId == grade.GradeId);
            var classList = _mysqlContext.ClassEntities.Where(c=>c.GradeId==gr.GradeId).ToList();
            var students = _mysqlContext.Students.ToList();

            var depList = _mysqlContext.Departments.ToList();
            var bedList = _mysqlContext.Bedrooms.ToList();

            var res = from cr in students
                      join cl in classList on cr.ClassEntityId equals cl.ClassEntityId
                      join dl in depList on cl.DepartmentId equals dl.DepartmentId
                      join bl in bedList on cr.BedRoomId equals bl.BedRoomId
                      into cv
                      from sl in cv.DefaultIfEmpty()
                      select new
                      {
                          cr.StudentId,
                          cr.StudentName,
                          cr.StudentNum,
                          cr.Sex,
                          cl.ClassName,
                          cl.Grade.GradeName,
                          dl.DepName,
                          sl.BedRoomName,
                          cr.IDNumber,
                          cr.Birthday,
                          cr.Nation,
                          cr.Political,
                          cr.FatherName,
                          cr.FatherPhone,
                          cr.MotherName,
                          cr.MotherPhone,
                          cr.Address
                      };
            var studentStr = JsonSerializer.Serialize(res);
            return Json(studentStr);
        }


        [HttpPost]
        public IActionResult GetStudentByDepartmentId(Department department)
        {
            var dep = _mysqlContext.Departments.FirstOrDefault(d => d.DepartmentId == department.DepartmentId);
            var classList = _mysqlContext.ClassEntities.Where(c => c.DepartmentId == dep.DepartmentId).ToList();
            var studentList=_mysqlContext.Students.ToList();

            var gradeList=_mysqlContext.Grades.ToList();
            var bedList = _mysqlContext.Bedrooms.ToList();

            var res = from cr in studentList
                      join cl in classList on cr.ClassEntityId equals cl.ClassEntityId
                      join gl in gradeList on cl.GradeId equals gl.GradeId
                      join bl in bedList on cr.BedRoomId equals bl.BedRoomId
                      into cv
                      from sl in cv.DefaultIfEmpty()
                      select new
                      {
                          cr.StudentId,
                          cr.StudentName,
                          cr.StudentNum,
                          cr.Sex,
                          cl.ClassName,
                          cl.Grade.GradeName,
                          cl.Department.DepName,
                          sl.BedRoomName,
                          cr.IDNumber,
                          cr.Birthday,
                          cr.Nation,
                          cr.Political,
                          cr.FatherName,
                          cr.FatherPhone,
                          cr.MotherName,
                          cr.MotherPhone,
                          cr.Address
                      };
            var studentStr = JsonSerializer.Serialize(res);
            return Json(studentStr);
        }

        [HttpPost]
        public IActionResult GetStudentByGradeIdAndDepartmentId(int gradeId,int departmentId) 
        {
            var grade = _mysqlContext.Grades.FirstOrDefault(g => g.GradeId == gradeId);
            var dep = _mysqlContext.Departments.FirstOrDefault(d => d.DepartmentId == departmentId);

            var classList = _mysqlContext.ClassEntities.Where(c => c.DepartmentId == dep.DepartmentId&&c.GradeId==grade.GradeId).ToList();
            var studentList = _mysqlContext.Students.ToList();
            var bedList = _mysqlContext.Bedrooms.ToList();


            var res = from cr in studentList
                      join cl in classList on cr.ClassEntityId equals cl.ClassEntityId
                      join bl in bedList on cr.BedRoomId equals bl.BedRoomId
                      into cv
                      from sl in cv.DefaultIfEmpty()
                      select new
                      {
                          cr.StudentId,
                          cr.StudentName,
                          cr.StudentNum,
                          cr.Sex,
                          cl.ClassName,
                          cl.Grade.GradeName,
                          cl.Department.DepName,
                          cr.BedRoom.BedRoomName,
                          cr.IDNumber,
                          cr.Birthday,
                          cr.Nation,
                          cr.Political,
                          cr.FatherName,
                          cr.FatherPhone,
                          cr.MotherName,
                          cr.MotherPhone,
                          cr.Address
                      };
            var studentStr = JsonSerializer.Serialize(res);
            return Json(studentStr);
        }


        [HttpPost]
        public IActionResult GetStudentByClassEntityId(ClassEntity classEntity)
        {
            var studentList=_mysqlContext.Students.Where(s=>s.ClassEntityId==classEntity.ClassEntityId).ToList();
            var classList = _mysqlContext.ClassEntities.ToList();
            var gradeList = _mysqlContext.Grades.ToList();
            var depList = _mysqlContext.Departments.ToList();
            var bedList = _mysqlContext.Bedrooms.ToList();


            var res = from cr in studentList
                      join cl in classList on cr.ClassEntityId equals cl.ClassEntityId
                      join gl in gradeList on cl.GradeId equals gl.GradeId
                      join bl in bedList on cr.BedRoomId equals bl.BedRoomId
                      join dl in depList on cl.DepartmentId equals dl.DepartmentId  
                      into cv
                      from sl in cv.DefaultIfEmpty()
                      select new
                      {
                          cr.StudentId,
                          cr.StudentName,
                          cr.StudentNum,
                          cr.Sex,
                          cl.ClassName,
                          gl.GradeName,
                          sl.DepName,
                          cr.BedRoom.BedRoomName,
                          cr.IDNumber,
                          cr.Birthday,
                          cr.Nation,
                          cr.Political,
                          cr.FatherName,
                          cr.FatherPhone,
                          cr.MotherName,
                          cr.MotherPhone,
                          cr.Address
                      };
            var studentStr = JsonSerializer.Serialize(res);
            return Json(studentStr);

        }
    }
}
